﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.IO;
using System.Text;
using System.Windows.Forms;
//using System.Data.SqlServerCe;
using System.Data.SqlClient;
using GoogleChartSharp;

namespace BiSA
{
    public partial class BiSA : Form
    {
        public BiSA()
        {
            InitializeComponent();
        }

       private void BiSA_Load(object sender, EventArgs e)
        {
        
        }
        private void LoadCellLines()
        {
            
            string sql = "Select a.UserDataDetailId, a.DataLabel + ' (' + Cast(B.TotalCount as varchar(50)) + ')' as DataLabel from UserDataDetails A ";
            sql +="inner join (select UserDataDetailId, COUNT(UserDataDetailId) TotalCount  from UserTFSites group by UserDataDetailId) ";
            sql +=" As B on a.UserDataDetailId=b.UserDataDetailId ";
            //Load uploaded data
            SqlCommand cm = MKDataAccesser.CreateCommand(sql.ToString(), CommandType.Text);
            DataSet ds = MKDataAccesser.GetRecord(cm);
            cbUploadDataLabel.DisplayMember = "DataLabel";
            cbUploadDataLabel.ValueMember = "UserDataDetailId";
            cbUploadDataLabel.DataSource = ds.Tables[0];

            //Loads cell lines
            sql ="Select A.TFBindingSitesDetailsId, a.Organism + '->' + a.CellLine + '->' + a.TFName ";
            sql += " + ' (' + Cast(B.TotalCount as varchar(50)) + ')'  as TFLabel from TFBindingSitesDetails A "; 
            sql +=" inner join (select TFBindingSitesDetailsId, COUNT(TFBindingSitesDetailsId) TotalCount  from TFBindingSites group by TFBindingSitesDetailsId) ";
            sql += " As B on a.TFBindingSitesDetailsId=b.TFBindingSitesDetailsId where  A.Active=1 ";

            cm = MKDataAccesser.CreateCommand(sql, CommandType.Text);
            ds = MKDataAccesser.GetRecord(cm);
            //DataRow dr = ds.Tables[0].NewRow();
            //dr["CellLine"] = "All";
            //dr["CellLineDesc"] = "All Active";
            //ds.Tables[0].Rows.InsertAt(dr, 0);
            cbCellLine.DisplayMember = "TFLabel";
            cbCellLine.ValueMember = "TFBindingSitesDetailsId";
            cbCellLine.DataSource = ds.Tables[0];
        }

       private void button1_Click(object sender, EventArgs e)
       {
           // SQL Server Management Objects (SMO) SQL Server 2008 R2 Other Versions 
           //http://msdn.microsoft.com/en-us/library/ms162169.aspx
           if (!MKCommon.IsNumeric(txtBPrange.Text))
           {
               MessageBox.Show("The value for BP Overlap could only be a positive or negative integer.", "BP Overlap Value Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
               return; 
           }
           if (!MKCommon.IsNumeric(txtCentreDistance.Text) && !txtCentreDistance.Text.Trim().Equals(""))
           {
               MessageBox.Show("The value for distance between two centres could only be a positive integer.", "CentreDistance Value Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
               return;
           }
           if (rbSimplePerc.Checked)
               GetAllSimplePercCount();
           else if (rbCommonOverlaps.Checked)
               GetAllCommonOverlapedSites();
           else if (rbNotOverlapKB.Checked || rbNotOverlapUser.Checked)
               GetAllNotOverlapSites();
           else
           {
               Cursor.Current = Cursors.WaitCursor;
               button1.Enabled = false;
               SqlCommand cm = MKDataAccesser.CreateCommand("sp_GetSitesByTFID", CommandType.StoredProcedure);
               cm.Parameters.Add(new SqlParameter("@UserDataDetailId", cbUploadDataLabel.SelectedValue.ToString()));
               cm.Parameters.Add(new SqlParameter("@bpOverlap", txtBPrange.Text));
               cm.Parameters.Add(new SqlParameter("@CentreDistance", txtCentreDistance.Text));
               cm.Parameters.Add(new SqlParameter("@TFBindingSitesDetailsId", cbCellLine.SelectedValue.ToString()));
               cm.Parameters.Add(new SqlParameter("@Chr", cbChr.SelectedValue.ToString()));

               SqlParameter param = new SqlParameter("@TotalMatched", SqlDbType.VarChar, 50);
               param.Direction = ParameterDirection.Output;
               cm.Parameters.Add(param);

               DataSet ds = MKDataAccesser.GetRecord(cm);
               dataGridView1.DataSource = ds.Tables[0];
               Cursor.Current = Cursors.Default;
               button1.Enabled = true;
               string _TotalMatched = cm.Parameters["@TotalMatched"].Value.ToString();
               lblResults.Text = "Total records matched: " + _TotalMatched;
           }
       }

       private void GetAllNotOverlapSites()
       {
               Cursor.Current = Cursors.WaitCursor;
               button1.Enabled = false;
               SqlCommand cm = MKDataAccesser.CreateCommand("sp_GetSitesNotOverlap", CommandType.StoredProcedure);
               cm.Parameters.Add(new SqlParameter("@UserDataDetailId", cbUploadDataLabel.SelectedValue.ToString()));
               cm.Parameters.Add(new SqlParameter("@bpOverlap", txtBPrange.Text));
               cm.Parameters.Add(new SqlParameter("@TFBindingSitesDetailsId", cbCellLine.SelectedValue.ToString()));
               cm.Parameters.Add(new SqlParameter("@Chr", cbChr.SelectedValue.ToString()));
               if(rbNotOverlapKB.Checked)    
                   cm.Parameters.Add(new SqlParameter("@SitesFor", "KB"));
               else
                   cm.Parameters.Add(new SqlParameter("@SitesFor", "User"));

               SqlParameter param = new SqlParameter("@TotalMatched", SqlDbType.VarChar, 50);
               param.Direction = ParameterDirection.Output;
               cm.Parameters.Add(param);

               DataSet ds = MKDataAccesser.GetRecord(cm);
               dataGridView1.DataSource = ds.Tables[0];
               Cursor.Current = Cursors.Default;
               button1.Enabled = true;
               string _TotalMatched = cm.Parameters["@TotalMatched"].Value.ToString();
               lblResults.Text = "Total records matched: " + _TotalMatched;

       }

       private void GetAllCommonOverlapedSites()
       {
           Cursor.Current = Cursors.WaitCursor; button1.Enabled = false;
           SqlCommand cm = MKDataAccesser.CreateCommand("sp_GetAllCommonOverlapSites", CommandType.StoredProcedure);
           cm.Parameters.Add(new SqlParameter("@UserDataDetailId", cbUploadDataLabel.SelectedValue.ToString()));
           cm.Parameters.Add(new SqlParameter("@bpOverlap", txtBPrange.Text));
           cm.Parameters.Add(new SqlParameter("@CentreDistance", txtCentreDistance.Text));
           SqlParameter param = new SqlParameter("@TotalMatched", SqlDbType.VarChar, 50);
           param.Direction = ParameterDirection.Output;
           cm.Parameters.Add(param);

           DataSet ds = MKDataAccesser.GetRecord(cm);
           dataGridView1.DataSource = ds.Tables[0];
           lblResults.Text = "Total overlaped sites: " + ds.Tables[0].Rows.Count.ToString(); // lblResults.Text = "Total User Sites: " + cm.Parameters["@TotalUserSites"].Value.ToString();
           Cursor.Current = Cursors.Default; button1.Enabled = true;
       }

       private void GetAllSimplePercCount()
       {
           Cursor.Current = Cursors.WaitCursor; button1.Enabled = false;
           SqlCommand cm = MKDataAccesser.CreateCommand("sp_TotalOverlapCount", CommandType.StoredProcedure);
           cm.Parameters.Add(new SqlParameter("@UserDataDetailId", cbUploadDataLabel.SelectedValue.ToString()));
           cm.Parameters.Add(new SqlParameter("@bpOverlap", txtBPrange.Text));
           cm.Parameters.Add(new SqlParameter("@CentreDistance", txtCentreDistance.Text));
           SqlParameter param = new SqlParameter("@TotalUserSites", SqlDbType.VarChar, 50);
           param.Direction = ParameterDirection.Output;
           cm.Parameters.Add(param);

           DataSet ds = MKDataAccesser.GetRecord(cm);
           dataGridView1.DataSource = ds.Tables[0];
           lblResults.Text = "Total User Sites: " + cm.Parameters["@TotalUserSites"].Value.ToString();
           Cursor.Current = Cursors.Default; button1.Enabled = true;
       }

       private void cbUploadDataLabel_SelectedIndexChanged(object sender, EventArgs e)
       {

           String sql = "select Chr from UserTFSites where UserDataDetailId='" + cbUploadDataLabel.SelectedValue.ToString() + "' group by Chr order by Chr";
           SqlCommand cm = MKDataAccesser.CreateCommand(sql, CommandType.Text);
           DataSet ds = MKDataAccesser.GetRecord(cm);
           
           DataRow dr = ds.Tables[0].NewRow();
           dr["Chr"] = "All";
           ds.Tables[0].Rows.InsertAt(dr, 0);

           cbChr.DisplayMember = "Chr";
           cbChr.ValueMember = "Chr";
           cbChr.DataSource = ds.Tables[0];
       
       }

       private void btnBrowserUser_Click(object sender, EventArgs e)
       {
           
           OpenFileDialog fDialog = new OpenFileDialog();
           fDialog.Title = "Please select a file.";
           fDialog.Filter = "Bed/GFF Files (*.bed;*.gff)|*.gff;*.bed|All files (*.*)|*.*";
           if (fDialog.ShowDialog() == DialogResult.OK)
           {
               lblFileName.Text = fDialog.FileName.ToString();
           }
       }

       private void btnImportData_Click(object sender, EventArgs e)
       {//for threading seee http://www.dotnetperls.com/backgroundworker
           string ext = "";
           if (lblFileName.Text == "...")
           {
               MessageBox.Show("Please select a file to upload.", "BiSA Alert", MessageBoxButtons.OK, MessageBoxIcon.Error);
           }
           else
           {
               btnImportData.Enabled = false; Cursor.Current = Cursors.WaitCursor;
               try
               {
                   System.IO.FileInfo fi = new System.IO.FileInfo(lblFileName.Text);
                   ext = fi.Extension.ToLower();
               }
               catch (Exception exp) { MessageBox.Show(exp.Message, "Error!", MessageBoxButtons.OK, MessageBoxIcon.Error); return; }
               if (ext == ".gff" || ext == ".bed")
                   backgroundWorkerUser.RunWorkerAsync(ext);
               else
               {
                   MessageBoxManager.OK = "BED file";
                   MessageBoxManager.Cancel = "GFF File";
                   MessageBoxManager.Register();

                   DialogResult dr = MessageBox.Show("Is this a BED or GFF file?", "Specify the file format", MessageBoxButtons.OKCancel, MessageBoxIcon.Question);
                   if (dr == DialogResult.OK)
                       backgroundWorkerUser.RunWorkerAsync(".bed");
                   else
                       backgroundWorkerUser.RunWorkerAsync(".gff");
                   MessageBoxManager.Unregister();
               }
           }

       }
       private void btnImportKB_Click(object sender, EventArgs e)
       {
           string ext = "";
           if (lblFileNameKB.Text == "...")
           {
               MessageBox.Show("Please select a file to upload.", "BiSA Alert", MessageBoxButtons.OK, MessageBoxIcon.Error);
           }
           else
           {
               btnImportKB.Enabled = false; Cursor.Current = Cursors.WaitCursor;
               try
               {
                   System.IO.FileInfo fi = new System.IO.FileInfo(lblFileNameKB.Text);
                   ext = fi.Extension.ToLower();
               }
               catch (Exception exp)
               {
                   MessageBox.Show(exp.Message, "Error!", MessageBoxButtons.OK, MessageBoxIcon.Error);
               }

               if (ext == ".gff" || ext == ".bed")
                   backgroundWorkerKB.RunWorkerAsync(ext);
               else
               {
                   MessageBoxManager.OK = "BED file";
                   MessageBoxManager.Cancel = "GFF File";
                   MessageBoxManager.Register();

                   DialogResult dr = MessageBox.Show("Is this a BED or GFF file?", "Specify the file format", MessageBoxButtons.OKCancel, MessageBoxIcon.Question);
                   if (dr == DialogResult.OK)
                       backgroundWorkerKB.RunWorkerAsync(".bed");
                   else
                       backgroundWorkerKB.RunWorkerAsync(".gff");
                   MessageBoxManager.Unregister();
                   
               }


           }
       }
        private void btnPreview_Click(object sender, EventArgs e)
        {
            if (lblFileName.Text == "...")
            {
                MessageBox.Show("Please select a file to upload.", "BiSA Alert", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
            else
            {
                txtOutput.Text = "";
                string line;
                System.IO.StreamReader file = new System.IO.StreamReader(lblFileName.Text);
                for (int i = 0; i < 100; i++)
                {
                    line = file.ReadLine();
                    txtOutput.Text += line + "\r\n";
                }
            }
        }

        private void LoadAllKB()
        {
            SqlCommand cm = MKDataAccesser.CreateCommand("select Active, Organism, CellLine, TFName, RefGenome, TFBindingSitesDetailsId from TFBindingSitesDetails", CommandType.Text);
            DataSet ds = MKDataAccesser.GetRecord(cm);
            dgKB.DataSource = ds.Tables[0];
            //DataGridViewLinkColumn dl = new DataGridViewLinkColumn(); DataGridViewLinkCell dcell;
            //dcell = dl.CellTemplate;

            //dgKB.Columns["RefLink"].CellTemplate = dl.CellTemplate;
            //dgKB.Columns["TFBindingSitesDetailsId"].Visible = false;            
        }
        void tabControl1_SelectedIndexChanged(object sender, System.EventArgs e)
        {

            //switch(tabControl1.SelectedIndex){
            switch (tabControl1.SelectedTab.Name)
            {
                case "tabAnalysis":
                    LoadCellLines();
                    break;
                case "tabKB":
                    LoadAllKB();
                    //MessageBox.Show("index changed"  );
                    break;
                case "tabAdmin":
                    LoadAdmin();
                    //MessageBox.Show("index changed"  );
                    break;
                default:
                    //dostuff;
                    break;
            }            
        }

        private void LoadAdmin()
        {
            string sql="select TFBindingSitesDetailsId, ISNULL(Organism,'')+'-->'+ ISNULL(CellLine,'')+'-->' + ";
            sql += " ISNULL(TFName,'') + Case Active When 1 Then ' (Active)'	Else ' (Not Active)' End as TF from TFBindingSitesDetails ";
            SqlCommand cm = MKDataAccesser.CreateCommand(sql, CommandType.Text);           
            DataSet ds = MKDataAccesser.GetRecord(cm);
            clKB.DataSource = ds.Tables[0];
            clKB.DisplayMember = "TF";

            sql = "select UserDataDetailId, ISNULL(DataLabel,'')+' '+ ISNULL(DataDescription,'') as TF from UserDataDetails";
            cm = MKDataAccesser.CreateCommand(sql, CommandType.Text);
            ds = MKDataAccesser.GetRecord(cm);
            clUserData.DataSource = ds.Tables[0];
            clUserData.DisplayMember = "TF";
            //cbRoundNum.Items.Add("1"); cbRoundNum.Items.Add("2"); cbRoundNum.Items.Add("3");
            cbRoundNum.SelectedIndex = 0; //select first item as default.
            
        }

        private void chkSelectAll_CheckedChanged(object sender, EventArgs e)
        {
            DataGridViewCheckBoxCell dc = new DataGridViewCheckBoxCell();
            foreach (DataGridViewRow dr in dgKB.Rows)
            {
                //MessageBox.Show(dr.Cells[0].ToString());
                dc = (DataGridViewCheckBoxCell) dr.Cells[0];
                if (chkSelectAll.Checked && dc.Value.Equals(false))
                    dc.Value = true;
                else if (!chkSelectAll.Checked && dc.Value.Equals(true))
                    dc.Value = false;

            }
        }

        private void dgKB_CellContentClick(object sender, DataGridViewCellEventArgs e)
        {
            DataGridViewRow dr =  dgKB.Rows[e.RowIndex];
            string TFID = dr.Cells[5].Value.ToString();
            SqlCommand cm = MKDataAccesser.CreateCommand("select * from TFBindingSitesDetails where TFBindingSitesDetailsId=" + TFID, CommandType.Text);
            DataSet ds = MKDataAccesser.GetRecord(cm);
            foreach (DataRow drow in ds.Tables[0].Rows)
            {
                lkRefLink.Text = drow["RefLink"].ToString();
                lkRawDataLink.Text = drow["RawDataLink"].ToString();
                txtNotesKB.Text = drow["Notes"].ToString();
            }
        }

        private void btnUpdate_Click(object sender, EventArgs e)
        {
            string sql = "";
            SqlCommand cmd = new SqlCommand();
            
            DataGridViewCheckBoxCell dc = new DataGridViewCheckBoxCell();
            foreach (DataGridViewRow dr in dgKB.Rows)
            {
                
                dc = (DataGridViewCheckBoxCell)dr.Cells[0];
                sql = "UPDATE TFBindingSitesDetails SET ACTIVE=" + MKCommon.BoolToNumStr(dc.Value.ToString());
                sql += " WHERE TFBindingSitesDetailsId= " + dr.Cells["TFBindingSitesDetailsId"].Value.ToString();
                cmd = MKDataAccesser.CreateCommand(sql, CommandType.Text);
                MKDataAccesser.ExecuteMyQuery(cmd);
            }
            MessageBox.Show("Knowledge Base (KB) has been updated.");
        }

        private void btnPreviewKB_Click(object sender, EventArgs e)
        {
            if (lblFileNameKB.Text == "...")
            {
                MessageBox.Show("Please select a file to upload.", "BiSA Alert", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
            else
            {
                string line;
                txtOutPutKB.Text = "";
                System.IO.StreamReader file = new System.IO.StreamReader(lblFileNameKB.Text);
                for (int i = 0; i < 100; i++)
                {
                    line = file.ReadLine();
                    txtOutPutKB.Text += line + "\r\n";
                }
            }
        }


        private void btnBrowseUser_Click(object sender, EventArgs e)
        {

            OpenFileDialog fDialog = new OpenFileDialog();
            fDialog.Title = "Please select a file having BED or GFF extension.";
            fDialog.Filter = "Bed/GFF Files (*.bed;*.gff)|*.gff;*.bed|All files (*.*)|*.*";
            if (fDialog.ShowDialog() == DialogResult.OK)
            {
                lblFileNameKB.Text = fDialog.FileName.ToString();
            }
        }

        private void backgroundWorkerUser_DoWork(object sender, DoWorkEventArgs e)
        {
            SqlCommand cm = MKDataAccesser.CreateCommand("sp_InsertUserData", CommandType.StoredProcedure);
            cm.Parameters.Add(new SqlParameter("@DataLabel", txtDataLabel.Text));
            cm.Parameters.Add(new SqlParameter("@DataDescription", txtDataDescription.Text));

            SqlParameter param = new SqlParameter("@UserDataDetailId", SqlDbType.VarChar, 500);
            param.Direction = ParameterDirection.Output;
            cm.Parameters.Add(param);

            Hashtable ht = MKDataAccesser.ExecuteScalar(cm);
            string UserDataDetailId = cm.Parameters["@UserDataDetailId"].Value.ToString();
               
            string sql = ""; bool isGFF = false;
            string _output = "Time Started: " + DateTime.Now.ToString() + "\r\n";
            string[] read;  
            int _counter = 1, start, end;
            string line;
            int _recIgnored = 0;
            SqlCommand cmd = new SqlCommand();
            Cursor.Current = Cursors.WaitCursor;
            
            // Read the file and split it line by line.
            char _delimter = '\t';
            if (rbComma.Checked) _delimter = ',';
            System.IO.FileInfo fi; int _TotalLines;
            try {
                fi = new System.IO.FileInfo(lblFileName.Text);
                _TotalLines = System.IO.File.ReadAllLines(lblFileName.Text).Length;
            }
            catch (Exception ex) { MessageBox.Show(ex.Message, "Error!", MessageBoxButtons.OK, MessageBoxIcon.Error); e.Cancel = true; return; }
            
            
            string ext = (string)e.Argument;
            if (ext == ".gff") isGFF = true;
            System.IO.StreamReader file = new System.IO.StreamReader(lblFileName.Text);
            while ((line = file.ReadLine()) != null)
            {

                try
                {

                    read = line.Split(_delimter);
                    sql = "INSERT INTO UserTFSites (UserDataDetailId , Chr, Start, [End]) VALUES (";
                    //Convert.ToInt32(read[1]) could generate an error.
                    if (isGFF)
                    {
                        sql += UserDataDetailId + ",'" + read[0] + "', " + Convert.ToInt32(read[3]) + "," + Convert.ToInt32(read[4]) + ")";
                    }
                    else  //then it is bed file
                    {
                        start = Convert.ToInt32(read[1]) + 1; end = Convert.ToInt32(read[2]);
                        sql += UserDataDetailId + ",'" + read[0] + "', " + start + "," + end + ")";
                    }

                    //    System.Diagnostics.Debug.WriteLine(read[0]+"', " + read[1] + ","+ read[2]);
                    cmd = MKDataAccesser.CreateCommand(sql, CommandType.Text);
                    MKDataAccesser.ExecuteMyQuery(cmd);
                }

                catch
                {

                    _recIgnored++;
                    _output += "Line " + _counter.ToString() + " ignored: " + line + "\r\n";
                }
                _counter++;
                backgroundWorkerUser.ReportProgress(_counter * 100 / _TotalLines);
                
            }
            file.Close();
            
            _output += "Total records processed:" + _counter.ToString() + "\r\n";
            _output += "Total records ignored:" + _recIgnored.ToString() + "\r\n";
            _output += "Time Finished: " + DateTime.Now.ToString();
            try
            {
                e.Result = _output;  //this value is retreived in RunWorkerCompleted
            }
            catch (Exception exp){
               // txtOutput.Text = exp.Message;
            }
        }


        private void backgroundWorkerUser_ProgressChanged(object sender, ProgressChangedEventArgs e)
        {
            
            //toolStripProgressBarUser.Value = e.ProgressPercentage;
        }

        private void backgroundWorkerUser_RunWorkerCompleted(object sender, RunWorkerCompletedEventArgs e)
        {
            btnImportData.Enabled = true;
            toolStripProgressBarUser.Value = 0;
            txtOutput.Text = (string)e.Result;
            lblFileName.Text = "...";
            txtDataLabel.Text = "";
            txtDataDescription.Text = "";
            if(!e.Cancelled)
                MessageBox.Show("User data import has been successfully completed.");
        }

        private void backgroundWorkerKB_DoWork(object sender, DoWorkEventArgs e)
        {
            SqlCommand cm = MKDataAccesser.CreateCommand("sp_InsertKBData", CommandType.StoredProcedure);
            cm.Parameters.Add(new SqlParameter("@Organism", txtOrganism.Text));
            cm.Parameters.Add(new SqlParameter("@TFName", txtTFName.Text));
            cm.Parameters.Add(new SqlParameter("@RefGenome", txtRefGenome.Text));
            cm.Parameters.Add(new SqlParameter("@CellLine", txtCellLine.Text));
            cm.Parameters.Add(new SqlParameter("@RefLink", txtRefLink.Text));
            cm.Parameters.Add(new SqlParameter("@RawDataLink", txtRawDataLink.Text));
            cm.Parameters.Add(new SqlParameter("@Notes", txtNotes.Text));

            SqlParameter param = new SqlParameter("@TFBindingSitesDetailsId", SqlDbType.VarChar, 50);
            param.Direction = ParameterDirection.Output;
            cm.Parameters.Add(param);

            Hashtable ht = MKDataAccesser.ExecuteScalar(cm);
            string TFBindingSitesDetailsId = cm.Parameters["@TFBindingSitesDetailsId"].Value.ToString();

        

            string sql = ""; bool isGFF = false;
            string _output = "Time Started: " + DateTime.Now.ToString() + "\r\n";
            string[] read;
            int _counter = 1, start, end;
            string line;
            int _recIgnored = 0;
            SqlCommand cmd = new SqlCommand();

            // Read the file and split it line by line.
            char _delimter = '\t';
            if (rbComma.Checked) _delimter = ',';
            System.IO.FileInfo fi = new System.IO.FileInfo(lblFileNameKB.Text);
            int _TotalLines = System.IO.File.ReadAllLines(lblFileNameKB.Text).Length; //total lines
            string ext = (string)e.Argument;
            if (ext==".gff") isGFF = true;
            System.IO.StreamReader file = new System.IO.StreamReader(lblFileNameKB.Text);
            while ((line = file.ReadLine()) != null)
            {

                try
                {

                    read = line.Split(_delimter);
                    sql = "INSERT INTO TFBindingSites (TFBindingSitesDetailsId , Chr, Start, [End]) VALUES (";
                    //Convert.ToInt32(read[1]) could generate an error.
                    if (isGFF)
                    {
                        sql += TFBindingSitesDetailsId + ",'" + read[0] + "', " + Convert.ToInt32(read[3]) + "," + Convert.ToInt32(read[4]) + ")";
                    }
                    else  //then it is bed file
                    {
                        start = Convert.ToInt32(read[1]) + 1; end = Convert.ToInt32(read[2]);
                        sql += TFBindingSitesDetailsId + ",'" + read[0] + "', " + start + "," + end + ")";
                    }

                    cmd = MKDataAccesser.CreateCommand(sql, CommandType.Text);
                    MKDataAccesser.ExecuteMyQuery(cmd);
                }

                catch
                {

                    _recIgnored++;
                    _output += "Line " + _counter.ToString() + " ignored: " + line + "\r\n";
                }
                _counter++;
                backgroundWorkerKB.ReportProgress(_counter * 100 / _TotalLines);

            }
            file.Close();

            _output += "Total records processed:" + _counter.ToString() + "\r\n";
            _output += "Total records ignored:" + _recIgnored.ToString() + "\r\n";
            _output += "Time Finished: " + DateTime.Now.ToString();
            e.Result = _output;  //this value is retreived in RunWorkerCompleted
        }

        private void backgroundWorkerKB_ProgressChanged(object sender, ProgressChangedEventArgs e)
        {
            //toolStripProgressBarKB.Value = e.ProgressPercentage;
        }

        private void backgroundWorkerKB_RunWorkerCompleted(object sender, RunWorkerCompletedEventArgs e)
        {
            btnImportKB.Enabled = true; Cursor.Current = Cursors.Default;
            toolStripProgressBarKB.Value = 0;
            txtOutPutKB.Text = (string)e.Result;
            lblFileNameKB.Text = "..."; txtTFName.Text = ""; txtCellLine.Text = ""; txtRefLink.Text = ""; txtRawDataLink.Text = "";
            MessageBox.Show("Knowledge Base (KB) data import has been successfully completed.");
        }

        private void rbAdvOverlap_CheckedChanged(object sender, EventArgs e)
        {
            if (rbAdvOverlap.Checked) { cbCellLine.Enabled = true; cbChr.Enabled = true; txtCentreDistance.Enabled = true; }
        }

        private void rbCommonOverlaps_CheckedChanged(object sender, EventArgs e)
        {
            if (rbCommonOverlaps.Checked) { cbCellLine.Enabled = false; cbChr.Enabled = false; txtCentreDistance.Enabled = true; }
        }

        private void rbSimplePerc_CheckedChanged(object sender, EventArgs e)
        {
            if (rbSimplePerc.Checked) { cbCellLine.Enabled = false; cbChr.Enabled = false; txtCentreDistance.Enabled = true; }
        }

        private void btnSave_Click(object sender, EventArgs e)
        {
            int start; //hold value of start of chromosome
            Stream myStream;
            SaveFileDialog saveFileDialog1 = new SaveFileDialog();

            saveFileDialog1.Filter = "Tab delimited Text files (*.txt)|*.txt";
            saveFileDialog1.RestoreDirectory = true;

            if (saveFileDialog1.ShowDialog() == DialogResult.OK)
            {
                if ((myStream = saveFileDialog1.OpenFile()) != null)
                {
                    StreamWriter wText = new StreamWriter(myStream);
                    //Save coloumn headers
                    if (BAL.SaveHeadings())
                    {
                        for (int i = 0; i < dataGridView1.Columns.Count; i++)
                        {
                            wText.Write(dataGridView1.Columns[i].HeaderText + "\t");
                        }
                        wText.Write(wText.NewLine);
                    }
                    for (int i = 0; i < dataGridView1.Rows.Count; i++)
                    {
                        DataGridViewRow row = dataGridView1.Rows[i];
                        for (int j = 0; j < row.Cells.Count; j++)
                        {
                            if(dataGridView1.Columns[row.Cells[j].ColumnIndex].HeaderText.StartsWith("Start"))
                            {
                                start = Convert.ToInt32(row.Cells[j].Value.ToString()); start--;  //BED file start index is zero
                                wText.Write(start.ToString() + "\t") ;
                            }
                            else
                                wText.Write(row.Cells[j].Value.ToString() + "\t");  //
                        }
                        wText.Write(wText.NewLine);
                    }
                    wText.Close();
                    myStream.Close();
                }
            }
        }

        private void btnDeleteKB_Click(object sender, EventArgs e)
        {
            DataTable dt = new DataTable("table"); dt.Columns.Add("Id");
            foreach (DataRowView o in clKB.CheckedItems)
            {
                dt.Rows.Add(o.Row[0].ToString());
            }
            SqlCommand cm = MKDataAccesser.CreateCommand("sp_DeleteKB", CommandType.StoredProcedure);
            SqlParameter param = cm.Parameters.AddWithValue("@TFDetailsID", dt); //Needed TVP
            param.SqlDbType = SqlDbType.Structured;
            MKDataAccesser.ExecuteScalar(cm);
            LoadAdmin();
        }

        private void btnUser_Click(object sender, EventArgs e)
        {
            DataTable dt = new DataTable("table"); dt.Columns.Add("Id");
            foreach (DataRowView o in clUserData.CheckedItems)
            {
                dt.Rows.Add(o.Row[0].ToString());
            }
            SqlCommand cm = MKDataAccesser.CreateCommand("sp_DeleteUserData", CommandType.StoredProcedure);
            SqlParameter param = cm.Parameters.AddWithValue("@TFDetailsID", dt); //Needed TVP
            param.SqlDbType = SqlDbType.Structured;
            MKDataAccesser.ExecuteScalar(cm);
            LoadAdmin();
        }

        private void btnSaveKB_Click(object sender, EventArgs e)
        {
            if(clKB.CheckedItems.Count>1){
                MessageBox.Show("Only one KB could be saved at a time.","",MessageBoxButtons.OK, MessageBoxIcon.Error);
                return;
            }
            else if(clKB.CheckedItems.Count==0){
                MessageBox.Show("Please check a KB to save","",MessageBoxButtons.OK, MessageBoxIcon.Error);
                return;
            }
            DataRowView drv=(DataRowView)clKB.CheckedItems[0];
            Stream myStream;
            SaveFileDialog saveFileDialog1 = new SaveFileDialog();

            saveFileDialog1.Filter = "Tab delimited Text files (*.txt)|*.txt";
            saveFileDialog1.RestoreDirectory = true;

            if (saveFileDialog1.ShowDialog() == DialogResult.OK)
            {
                if ((myStream = saveFileDialog1.OpenFile()) != null)
                {
                    StreamWriter wText = new StreamWriter(myStream);

                    SqlCommand cm = MKDataAccesser.CreateCommand("select * from TFBindingSites where TFBindingSitesDetailsId=@ID", CommandType.Text);
                    SqlParameter param = cm.Parameters.AddWithValue("@ID", drv.Row[0].ToString()); //Needed TVP
                    DataSet ds = MKDataAccesser.GetRecord(cm);
                    foreach (DataRow dr in ds.Tables[0].Rows)
                    {

                        wText.Write( dr["Chr"].ToString() + "\t" + dr["Start"].ToString() + "\t" + dr["End"].ToString());
                        wText.Write(wText.NewLine);
                    }
                    wText.Close();
                    myStream.Close();
                    MessageBox.Show( ds.Tables[0].Rows.Count.ToString() + " records have been saved.");
                }
            }
        }

        private void btnSaveUserData_Click(object sender, EventArgs e)
        {
            if (clUserData.CheckedItems.Count > 1)
            {
                MessageBox.Show("Only one user data could be saved at a time.", "", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return;
            }
            else if (clUserData.CheckedItems.Count == 0)
            {
                MessageBox.Show("Please check a user data to save", "", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return;
            }
            DataRowView drv = (DataRowView)clUserData.CheckedItems[0];
            Stream myStream;
            SaveFileDialog saveFileDialog1 = new SaveFileDialog();

            saveFileDialog1.Filter = "Tab delimited Text files (*.txt)|*.txt";
            saveFileDialog1.RestoreDirectory = true;

            if (saveFileDialog1.ShowDialog() == DialogResult.OK)
            {
                if ((myStream = saveFileDialog1.OpenFile()) != null)
                {
                    StreamWriter wText = new StreamWriter(myStream);

                    SqlCommand cm = MKDataAccesser.CreateCommand("select * from UserTFSites where UserDataDetailId=@ID", CommandType.Text);
                    SqlParameter param = cm.Parameters.AddWithValue("@ID", drv.Row[0].ToString()); //Needed TVP
                    DataSet ds = MKDataAccesser.GetRecord(cm);
                    foreach (DataRow dr in ds.Tables[0].Rows)
                    {

                        wText.Write(dr["Chr"].ToString() + "\t" + dr["Start"].ToString() + "\t" + dr["End"].ToString());
                        wText.Write(wText.NewLine);
                    }
                    wText.Close();
                    myStream.Close();
                    MessageBox.Show(ds.Tables[0].Rows.Count.ToString() + " records have been saved.");
                }
            }
        }

        private void lkRefLink_LinkClicked(object sender, LinkLabelLinkClickedEventArgs e)
        {
            System.Diagnostics.Process.Start("IExplore", lkRefLink.Text);
        }

        private void lkRawDataLink_LinkClicked(object sender, LinkLabelLinkClickedEventArgs e)
        {
            System.Diagnostics.Process.Start("IExplore", lkRawDataLink.Text);
        }

        private void rbNotOverlapKB_CheckedChanged(object sender, EventArgs e)
        {
            if (rbNotOverlapKB.Checked)
            {
                cbCellLine.Enabled = true; cbChr.Enabled = true;
                txtCentreDistance.Enabled = false;
            }
        }

        private void rbNotOverlapUser_CheckedChanged(object sender, EventArgs e)
        {
            if (rbNotOverlapUser.Checked)
            {
                cbCellLine.Enabled = true; cbChr.Enabled = true;
                txtCentreDistance.Enabled = false;
            }
        }
        private void btnViewUserData_Click(object sender, EventArgs e)
        {
            DataRowView drv = (DataRowView)clUserData.SelectedValue;
            lblResultsAdmin.Text = "Results: " + drv[1].ToString();
            LoadTagsCountAvg(drv[0].ToString(), "UserData");
        }
        private void btnViewKB_Click(object sender, EventArgs e)
        {
            DataRowView drv = (DataRowView)clKB.SelectedValue;
            lblResultsAdmin.Text = "Results: " + drv[1].ToString();
            LoadTagsCountAvg(drv[0].ToString(), "KB");
        }
        private void LoadTagsCountAvg(string TFId, string TagFor)
        {
            Cursor.Current = Cursors.WaitCursor; btnViewKB.Enabled = false;
            SqlCommand cm = MKDataAccesser.CreateCommand("TagCountAvg", CommandType.StoredProcedure);
            cm.Parameters.Add(new SqlParameter("@TFId", TFId));
            cm.Parameters.Add(new SqlParameter("@TagFor", TagFor));
            if (cbRoundNum.Text.Equals(""))
                cm.Parameters.Add(new SqlParameter("@RndNum", "0"));
            else
                cm.Parameters.Add(new SqlParameter("@RndNum", "-" + cbRoundNum.SelectedItem));

            SqlParameter param = new SqlParameter("@AvgTagSize", SqlDbType.VarChar, 50);
            param.Direction = ParameterDirection.Output;
            cm.Parameters.Add(param);

            DataSet ds = MKDataAccesser.GetRecord(cm);
            dataGridAdmin.DataSource = ds.Tables[0];
            Cursor.Current = Cursors.Default;
            button1.Enabled = true;
            string AvgTagSize = cm.Parameters["@AvgTagSize"].Value.ToString();
            lblAvg.Text = "Average Tag Size : " + AvgTagSize;
            btnViewKB.Enabled = true;
        }

        private void btnCleanUpDB_Click(object sender, EventArgs e)
        {
            BAL.CleanupDB();
        }

        private void btnShowChart_Click(object sender, EventArgs e)
        {
            if (cbCellLine.Items.Count > 2)
            {
                MessageBox.Show("BiSA Venn Diagram can only compare one user dataset against two KBs. Please go to Step-3 Browse KB tab and de-activiate " + (cbCellLine.Items.Count - 2).ToString() + " KBs", "BiSA", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return;
            }
            DataRowView item1 = (DataRowView) cbCellLine.Items[0];
            int i=0;
            i = BAL.GetOverlapCount(cbUploadDataLabel.SelectedValue.ToString(), item1[0].ToString(), txtBPrange.Text, txtCentreDistance.Text);
            UserDataDetails ud = new UserDataDetails(cbUploadDataLabel.SelectedValue.ToString());
            TFBindingSitesDetails tf1 = new TFBindingSitesDetails(item1[0].ToString());
            TFBindingSitesDetails tf2;
            
            GoogleChart.VennDiagram venn = new GoogleChart.VennDiagram();
            venn.CircleALabel = ud.DataLabel; venn.CircleALabelValue = ud.TotalIntervals;
            venn.IntersectionAB = i.ToString();
            venn.CircleBLabel = tf1.TFName; venn.CircleBLabelValue = tf1.TotalIntervals;

            if (cbCellLine.Items.Count == 2)
            {
                DataRowView item2 = (DataRowView)cbCellLine.Items[1];
                tf2 = new TFBindingSitesDetails(item2[0].ToString());
                venn.CircleCLabel = tf2.TFName; venn.CircleCLabelValue = tf2.TotalIntervals;
                i = BAL.GetOverlapCount(cbUploadDataLabel.SelectedValue.ToString(), item2[0].ToString(), txtBPrange.Text, txtCentreDistance.Text);
                venn.IntersectionAC = i.ToString();
                i = BAL.Get2KBOverlapCount(item1[0].ToString(), item2[0].ToString(), txtBPrange.Text, txtCentreDistance.Text);
                venn.IntersectionBC = i.ToString();
                i = BAL.GetAllCommonOverlap(cbUploadDataLabel.SelectedValue.ToString(), txtBPrange.Text, txtCentreDistance.Text);
                venn.IntersectionABC = i.ToString();
            }
            else
            {
                venn.CircleCLabelValue = "0";
            }
            GoogleChart gc = new GoogleChart();          

            string url = gc.generateVenn(venn, "600", "400");
            string OverlapInfo = "Total " + venn.CircleALabel + " Binding Sites=" + venn.CircleALabelValue;
            OverlapInfo += "\r\nTotal " + venn.CircleBLabel + " Binding Sites=" + venn.CircleBLabelValue;
            OverlapInfo += "\r\nTotal " + venn.CircleCLabel + " Binding Sites=" + venn.CircleCLabelValue;

            OverlapInfo += "\r\nNo of " + venn.CircleALabel + " intervals overlaps with " + venn.CircleBLabel + " = " + venn.IntersectionAB;
            if (venn.CircleCLabelValue != "0") 
            {
                OverlapInfo += "\r\nNo of " + venn.CircleALabel + " intervals overlaps with " + venn.CircleCLabel + " = " + venn.IntersectionAC;
                OverlapInfo += "\r\nNo of " + venn.CircleBLabel + " intervals overlaps with " + venn.CircleCLabel + " = " + venn.IntersectionBC;
                OverlapInfo += "\r\nCommon overlaps of " +venn.CircleALabel + ", " + venn.CircleBLabel + " and " + venn.CircleCLabel + " = " + venn.IntersectionABC;
            }
            
            frmCharts cht = new frmCharts();
            cht.showChart(url, OverlapInfo);
            cht.ShowDialog();
            //System.Diagnostics.Process.Start("IExplore", lkRefLink.Text);
            
        }



    }
}


